package dao;

import BaseQuery.BaseQuery;
import Jdbc.Employee;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import until.DBUtils;
import until.JdbcUntil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EmployeeDao1 {
	public int add(Employee employee) throws SQLException {
		Connection connection=JdbcUntil.getConn();
		String sql="INSERT INTO emp(ename,job,hiredate,sal,deptno) VALUES (?,?,?,?,?);";
		return DBUtils.getQueryRunner().update(sql,employee.getEname(),employee.getJob(),
				employee.getHiredate(),employee.getSal(),employee.getDeptno());
	}
	public int update(Employee employee) throws SQLException {
		Connection connection=JdbcUntil.getConn();
		String sql="update emp set ename=?,job=?,hiredate=?,sal=?,deptno=? where empno=?";
		return DBUtils.getQueryRunner().update(sql,employee.getEname(),
				employee.getJob(),employee.getHiredate(),employee.getSal(),employee.getDeptno(),employee.getEmpno());
	}
	public Employee getById(Integer empno) throws SQLException {
		Connection connection=JdbcUntil.getConn();
		String sql="select empno,ename,job,hiredate,sal,deptno from emp where empno=?";
		return DBUtils.getQueryRunner().query(sql,new BeanHandler<Employee>(Employee.class),empno);
	}
	public List<Employee> getAll() throws  SQLException {
		String sql="select empno,ename,job,hiredate,sal,deptno from emp";
		return DBUtils.getQueryRunner().query(sql,new BeanListHandler<Employee>(Employee.class));
	}
	public List<Employee> getByEname(String enameCondition) throws Exception{
		String sql = "select empno,ename,job,hiredate,sal,deptno from emp where ename like CONCAT('%',?,'%')";
		return DBUtils.getQueryRunner().query(sql,new BeanListHandler<Employee>(Employee.class),enameCondition);
	}
	public List<Employee> getByPage( BaseQuery baseQuery) throws SQLException {
		String sql="select *from emp limit ?,?;";
		return DBUtils.getQueryRunner().query(sql,new BeanListHandler<Employee>(Employee.class),baseQuery.startIndex(),baseQuery.getPageCount());
	}
	public Integer getCount() throws SQLException {
		String sql="select count(empno) from emp";
		long count=DBUtils.getQueryRunner().query(sql,new ScalarHandler<Long>());
		return  (int ) count;
	}
}
